//
//  YbrSQLHelper.m
//  wifibox
//
//  Created by os X 10.9 on 14-11-13.
//  Copyright (c) 2014年 yinbaner. All rights reserved.
//  数据库管理

#import "YbrSQLHelper.h"
#define YBRDBNAME @"yinbaner.db"

@interface YbrSQLHelper ()
@end

@implementation YbrSQLHelper

static FMDatabaseQueue * queue;

// 产生数据库
-(void) createDb {
    // 音乐墙频道表
    NSString * channel_music_sql    =   @"CREATE TABLE IF NOT EXISTS music_channel(_id INTEGER PRIMARY KEY AUTOINCREMENT, id INTEGER , name TEXT ,course_type INTEGER , orderId INTEGER , selected SELECTED);";
    // 云课堂课程频道表
    NSString * channel_course_sql   =   @"CREATE TABLE IF NOT EXISTS course_channel(_id INTEGER PRIMARY KEY AUTOINCREMENT, id INTEGER , name TEXT ,course_type INTEGER ,orderId INTEGER , selected SELECTED);";
    // 本地音乐表(local_music  type :  0 手机本地的音乐 ,1课程   2SD卡   3网络  4本地Itus导入的)
    NSString * localmusic_sql       =   @"CREATE TABLE IF NOT EXISTS local_music (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name  TEXT,singername  TEXT,albumname TEXT,downloadurl TEXT,file  TEXT,img TEXT,parentfile TEXT,duration INTEGER ,timerstr TEXT,musicid TEXT ,size INTEGER ,type INTEGER);";
    // 本地课程表
    NSString * localcourse_sql      =   @"CREATE TABLE IF NOT EXISTS local_course (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,courseid INTEGER,channelid INTEGER,channelname  TEXT, channeltype INTEGER ,name  TEXT,img  TEXT)";
    // 本地课程和本地音乐的中间表
    NSString * localcourse_music_sql        =   @"CREATE TABLE IF NOT EXISTS local_course_Music (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,courseid  INTEGER,name  TEXT,downloadurl  TEXT,file  TEXT,timerstr  TEXT,duration INTEGER ,sort INTEGER ,musicid INTEGER);";
    // 有二级目录的本地课程和本地音乐的中间表
    NSString * localsecondcourse_music_sql  =   @"CREATE TABLE IF NOT EXISTS local_second_course_Music (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, classname TEXT(20) ,classsort INTEGER ,classid INTEGER ,courseid INTEGER,name  TEXT,downloadurl  TEXT,file  TEXT,timerstr  TEXT,duration INTEGER ,sort INTEGER ,musicid INTEGER);";
    // 临时的播放列表
    NSString * temp_playlist_sql            =   @"CREATE TABLE IF NOT EXISTS temp_playlist (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name  TEXT,singername TEXT,albumname  TEXT,downloadurl  TEXT,file  TEXT,img  TEXT,duration INTEGER , timerstr TEXT, musicid  TEXT ,type INTEGER);";
    // dlna的播放列表
    NSString * dlna_temp_playlist_sql       =   @"CREATE TABLE IF NOT EXISTS dlna_temp_playlist (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name  TEXT,singername TEXT,albumname  TEXT,downloadurl  TEXT,file  TEXT,img  TEXT,duration INTEGER , timerstr TEXT, musicid  TEXT ,type INTEGER);";
    // 用户自己的播放列表
    NSString * user_playlist_sql            =   @"CREATE TABLE IF NOT EXISTS user_playlist (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,sort INTEGER ,name  TEXT);";
    // 用户的播放列表和本地音乐的中间表
    NSString * user_playlist_localmusic_sql =   @"CREATE TABLE IF NOT EXISTS user_playlist_localmusic (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,playlistid INTEGER,name TEXT,singername  TEXT,albumname TEXT,downloadurl  TEXT,file TEXT,img TEXT,duration INTEGER ,type INTEGER ,timerstr TEXT,musicid TEXT);";
    // 我喜欢的音乐的列表
    NSString * fav_music_sql                =   @"CREATE TABLE IF NOT EXISTS fav_music (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,sort INTEGER ,name  TEXT);";
    // 我喜欢的音乐列表和在线音乐的中间表
    NSString * bgk_music_sql                =   @"CREATE TABLE IF NOT EXISTS bgk_music(_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT,url TEXT,timerstr  TEXT,duration INTEGER ,musicid  TEXT,favmusicid INTEGER);";
    // 我的书包
    // comeFrom 资源来自哪里
    // 2015-11-1 Jack Liu [add]
    NSString * my_bag_sql                   =   @"CREATE TABLE my_bag(_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT,courseid INTEGER ,img TEXT ,channelid INTEGER,channelname TEXT ,channeltype INTEGER ,musicstr TEXT,comeFrom INTEGER);";
    // 定时
    NSString * clock_sql                    =   @"CREATE TABLE clock (_id INTEGER PRIMARY KEY AUTOINCREMENT, uuid TEXT, name TEXT);";
    // 定时和时间的中间表
    NSString * clock_timer                  =   @"CREATE TABLE clock_timer (_id INTEGER PRIMARY KEY AUTOINCREMENT, timerid INTEGER,weekday INTEGER,beginTime INTEGER,endTime INTEGER,isopen INTEGER DEFAULT 1);";
    // 定时和音乐的中间表
    NSString * clock_music                  =   @"CREATE TABLE clock_music (_id INTEGER PRIMARY KEY AUTOINCREMENT, clocktime_id INTEGER ,type INTEGER ,name TEXT,url TEXT,duration INTEGER ,singername TEXT,albumname TEXT,file TEXT,img TEXT,musicid TEXT)";
    // 下载完成
    NSString * down_over                    =   @"CREATE TABLE down_over (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, music_id INTEGER ,downTime TEXT)";
    
    //录音时选择的背景音乐
    NSString * record_bgkMusic_sql          =   @"CREATE TABLE record_bgkMusic (_id INTEGER PRIMARY KEY AUTOINCREMENT, songName TEXT unique, music_id INTEGER ,songUrl TEXT,isSel INTEGER)";
    
    //保存在本地的录音文件
    NSString * record_final_sql             =   @"CREATE TABLE record_final(_id INTEGER PRIMARY KEY AUTOINCREMENT, recordName TEXT unique,recordUrl TEXT,recordImgUrl text,recordTime text,albumName text,albumLei TEXT,albumImgUrl text,currentTime text,playCount INTEGER,favCount INTEGER,isUpload integer,courseId integer,classId  integer)";
    
   //播放历史
    NSString * playHistory_sql              =   @"CREATE TABLE playHistory (_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,musicid TEXT,musicName TEXT not null,singername TEXT, albumId TEXT, albumname TEXT,downloadurl TEXT,file TEXT,img TEXT,duration INTEGER,type INTEGER,timerstr TEXT,timeLength TEXT,lastPlayTime TEXT)";
    //设备
    NSString *device_sql = @"create table device(_id integer primary key autoincrement not null,deviceName text not null,deviceIP text not null unique,userName text,deviceVersion text,isNeedUpgrade integer)";

    NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:YBRDBNAME];
//    // 1.创建数据库队列
    queue = [FMDatabaseQueue databaseQueueWithPath:filename];
    
    
    NSLog(@"---------huanghuanghuang-----------%@",filename);
    
    
    
    
    
    // 2.创表
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        [db executeUpdate:channel_music_sql];
        [db executeUpdate:channel_course_sql];
        [db executeUpdate:localmusic_sql];
        [db executeUpdate:localcourse_sql];
        [db executeUpdate:localcourse_music_sql];
        [db executeUpdate:localsecondcourse_music_sql];
        [db executeUpdate:dlna_temp_playlist_sql];
        [db executeUpdate:temp_playlist_sql];
        [db executeUpdate:user_playlist_sql];
        [db executeUpdate:user_playlist_localmusic_sql];
        [db executeUpdate:bgk_music_sql];
        [db executeUpdate:fav_music_sql];
        [db executeUpdate:my_bag_sql];
        
        [db executeUpdate:clock_sql];
        [db executeUpdate:clock_timer];
        [db executeUpdate:clock_music];
        [db executeUpdate:down_over];
        
        [db executeUpdate:record_bgkMusic_sql];
        [db executeUpdate:record_final_sql];
        [db executeUpdate:playHistory_sql];
        [db executeUpdate:device_sql];
    }];
}


+(FMDatabaseQueue *)getDBHandler{
    NSString *filename      = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:YBRDBNAME];
    FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath:filename];
    return queue;
}

// 这里采用了每次打开db数据库的操作，有可能回出现问题，后期处理
// 采用更好的编码方式
+(FMDatabaseQueue *)getDataBase{
    NSString *filename      = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:YBRDBNAME];
    FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath:filename];
    return queue;
}

@end
